﻿'Imports MS.Internal.ComAutomation
Imports System.Windows.Interop
Imports System.Runtime.InteropServices
Imports System.Runtime.InteropServices.Automation

Class ExcelHelper
    Implements IDisposable

    Private _excel As Object
    Private _workbook As Object
    Private WithEvents _workBookCloseEvent As AutomationEvent
    Private WithEvents _newWorkbookEvent As AutomationEvent
    Private WithEvents _openWorkbookEvent As AutomationEvent
    Private _displayAlertsOnQuit As Boolean = True

#Region "Constants"
    Const XlListObjectSourceType_xlSrcRange = 1
    Const XlYesNoGuess_xlYes = 1
    Const XlDVType_xlValidateList = 3
    Const _XlDVAlertStyle_xlValidAlertStop = 1
#End Region

    Public ReadOnly Property Excel
        Get
            Try
                If _excel Is Nothing Then
                    Me.CreateExcel()
                End If

            Catch ex As Exception
                Throw ex
            End Try
            Return _excel
        End Get
    End Property

    Public Sub Quit()
        Try
            If Not (_excel Is Nothing) Then

                _excel.DisplayAlerts = False

                If Not (_workbook Is Nothing) Then
                    _workbook.Close(False)
                End If

                _excel.Quit()
            End If
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Public Property Workbook
        Get
            Try
                If _workbook Is Nothing Then
                    _workbook = Excel.ActiveWorkbook
                End If
            Catch ex As Exception
                Throw ex
            End Try
            Return _workbook
        End Get
        Set(value)
            _workbook = value
        End Set
    End Property

    ' Use this method to retrieve active Word session
    Public Function GetExcel() As Boolean
        Try
            ' If GetObject throws an exception, then Word is 
            ' either not running or is not available.
            _excel = AutomationFactory.GetObject("Excel.Application")
            Me.AddWorkbookOpeningEvents()
            Return True
        Catch
            Return False
        End Try
    End Function

    ' Use this method to explicity create a new instance of Word
    Public Function CreateExcel() As Boolean
        Try
            ' If CreateObject throws an exception, then Word is not available.
            _excel = AutomationFactory.CreateObject("Excel.Application")
            Me.AddWorkbookOpeningEvents()
            Return True
        Catch
            Return False
        End Try
    End Function
    

    Public Sub OpenWorkbook(ByVal filePath As String)
        Dim o As Object
        o = Excel
        If o IsNot Nothing Then
            Workbook = o.WorkBooks.Open(filePath)
        Else
            Throw New InvalidProgramException("Error: Could not open Excel spreadsheet. Please check it is installed.")
        End If
    End Sub

    Public Sub CreateWorkbook()
        Dim o As Object
        o = Excel
        If o IsNot Nothing Then
            Workbook = o.Workbooks.Add()
        End If
    End Sub

    Public Sub Cells(ByVal RowIndex As Object, ByVal ColumnIndex As Object, ByVal value As Object)
        If Workbook IsNot Nothing Then
            Workbook.ActiveSheet.Cells(RowIndex, ColumnIndex).Value = value
        End If
    End Sub

    Public Sub CloseWorkbook()
        If Workbook IsNot Nothing Then
            Workbook.Save()
            Workbook.Close()
            Workbook = Nothing
        End If
    End Sub

    Public Sub ShowWorkbook()
        Dim o As Object
        o = Excel
        If o IsNot Nothing Then
            o.Visible = True
        End If
    End Sub

    Public ReadOnly Property GetWorkSheets(ByVal workBookIndex As Int32) As IEnumerable(Of ExcelWorkSheet)
        Get
            Dim workBook As Object = Excel.WorkBooks(workBookIndex)

            Dim workSheets As New List(Of ExcelWorkSheet)
            Dim index As Int32 = 1
            For Each workSheet In workBook.WorkSheets
                workSheets.Add(New ExcelWorkSheet(index, workSheet.Name))
                index = index + 1
            Next
            Return workSheets
        End Get
    End Property

    Public ReadOnly Property UsedRange(ByVal workBookIndex As Int32, ByVal workSheetIndex As Int32) As String(,)
        Get
            Dim workBook As Object = Excel.WorkBooks(workBookIndex)
            Dim workSheet As Object = workBook.WorkSheets(workSheetIndex)
            Dim excelRange = workSheet.UsedRange
            Dim columnCount As Int32 = excelRange.Columns.Count
            Dim rowCount As Int32 = excelRange.Rows.Count


            Dim valueArray(rowCount - 1, columnCount - 1) As String
            For i = 1 To rowCount
                For j = 1 To columnCount
                    valueArray(i - 1, j - 1) = excelRange(i, j).Value
                Next
            Next
            Return valueArray
        End Get
    End Property

    Public Sub AddWorkbookCloseEvent(wb As Object)
        If _workBookCloseEvent Is Nothing Then
            _workBookCloseEvent = AutomationFactory.GetEvent(wb, "BeforeClose")
            AddHandler _workBookCloseEvent.EventRaised, AddressOf WorkbookCloseEvent
        End If
    End Sub

    Public Sub AddWorkbookOpeningEvents()
        If _newWorkbookEvent Is Nothing Then
            _newWorkbookEvent = AutomationFactory.GetEvent(_excel, "NewWorkbook")
            AddHandler _newWorkbookEvent.EventRaised, AddressOf NewWorkbookEvent
        End If

        If _openWorkbookEvent Is Nothing Then
            _openWorkbookEvent = AutomationFactory.GetEvent(_excel, "WorkbookOpen")
            AddHandler _openWorkbookEvent.EventRaised, AddressOf OpenWorkbookEvent
        End If
    End Sub

    Public Sub RemoveEventHandlers()
        If Not _workBookCloseEvent Is Nothing Then
            RemoveHandler _workBookCloseEvent.EventRaised, AddressOf WorkbookCloseEvent
            _workBookCloseEvent = Nothing
        End If

        If Not _newWorkbookEvent Is Nothing Then
            RemoveHandler _newWorkbookEvent.EventRaised, AddressOf NewWorkbookEvent
            _newWorkbookEvent = Nothing
        End If

        If Not _openWorkbookEvent Is Nothing Then
            RemoveHandler _openWorkbookEvent.EventRaised, AddressOf OpenWorkbookEvent
            _openWorkbookEvent = Nothing
        End If
    End Sub

    Private Sub WorkbookCloseEvent(ByVal sender As Object, ByVal e As AutomationEventArgs) Handles _workBookCloseEvent.EventRaised
        Me._displayAlertsOnQuit = True
        Me.Dispose()
    End Sub

    Private Sub NewWorkbookEvent(ByVal sender As Object, ByVal e As AutomationEventArgs) Handles _newWorkbookEvent.EventRaised
        AddWorkbookCloseEvent(e.Arguments(0))
    End Sub

    Private Sub OpenWorkbookEvent(ByVal sender As Object, ByVal e As AutomationEventArgs) Handles _openWorkbookEvent.EventRaised
        AddWorkbookCloseEvent(e.Arguments(0))
    End Sub

#Region "IDisposable Support"
    Private disposedValue As Boolean ' To detect redundant calls

    ' IDisposable
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
                ' TODO: dispose managed state (managed objects).
            End If
            
            Excel.DisplayAlerts = Me._displayAlertsOnQuit
            Excel.Quit()


            ' TODO: free unmanaged resources (unmanaged objects) and override Finalize() below.
            ' TODO: set large fields to null.
            Me.RemoveEventHandlers()
            '_beforeCloseEvent.RemoveEventHandler(New ComAutomationEventHandler(AddressOf BeforeCloseEventHandler))
            '_beforeCloseEvent = Nothing
            _excel = Nothing

            GC.Collect()
            GC.WaitForPendingFinalizers()
        End If
        Me.disposedValue = True
    End Sub

    ' TODO: override Finalize() only if Dispose(ByVal disposing As Boolean) above has code to free unmanaged resources.
    'Protected Overrides Sub Finalize()
    '    ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
    '    Dispose(False)
    '    MyBase.Finalize()
    'End Sub

    ' This code added by Visual Basic to correctly implement the disposable pattern.
    Public Sub Dispose() Implements IDisposable.Dispose
        ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub
#End Region
End Class

Friend Class ExcelWorkSheet
    Public Sub New(ByVal index As Int32, ByVal name As String)
        Me.Index = index
        Me.Name = name
    End Sub
    Public Property Index As Int32
    Public Property Name As String
End Class
